CREATE TABLE [dbo].[Hierarchy]
(
[HierarchyKey] [uniqueidentifier] NOT NULL,
[RootHierarchyKey] [uniqueidentifier] NULL,
[ParentHierarchyKey] [uniqueidentifier] NULL,
[SortOrder] [int] NOT NULL,
[Depth] [int] NOT NULL,
[UniformType] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UniformKey] [uniqueidentifier] NULL,
[IsChildAMemberOfParent] [bit] NOT NULL CONSTRAINT [DF_Hierarchy_IsChildAMemberOfParent] DEFAULT ((1)),
[HierarchyCode] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MarkedForDeleteOn] [datetime] NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Hierarchy] ADD CONSTRAINT [PK_Hierarchy] PRIMARY KEY CLUSTERED ([HierarchyKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Hierarchy_HierarchyCode] ON [dbo].[Hierarchy] ([HierarchyCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Hierarchy_ParentHierarchyKey_HierarchyKey_UniformKey] ON [dbo].[Hierarchy] ([ParentHierarchyKey], [HierarchyKey], [UniformKey]) INCLUDE ([Depth], [HierarchyCode], [IsChildAMemberOfParent], [RootHierarchyKey], [SortOrder], [UniformType]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Hierarchy_ParentHierarchyKey_SortOrder_Depth_UniformKey] ON [dbo].[Hierarchy] ([ParentHierarchyKey], [SortOrder], [Depth], [UniformKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Hierarchy_RootHierarchyKey_ParentHierarchyKey] ON [dbo].[Hierarchy] ([RootHierarchyKey], [ParentHierarchyKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Hierarchy_RootHierarchyKey_SortOrder_Depth] ON [dbo].[Hierarchy] ([RootHierarchyKey], [SortOrder], [Depth]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Hierarchy_UniformKey_RootHierarchyKey_HierarchyKey] ON [dbo].[Hierarchy] ([UniformKey], [RootHierarchyKey], [HierarchyKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Hierarchy] ADD CONSTRAINT [FK_Hierarchy_Hierarchy_Ancestor] FOREIGN KEY ([RootHierarchyKey]) REFERENCES [dbo].[Hierarchy] ([HierarchyKey])
GO
ALTER TABLE [dbo].[Hierarchy] ADD CONSTRAINT [FK_Hierarchy_Hierarchy_Parent] FOREIGN KEY ([ParentHierarchyKey]) REFERENCES [dbo].[Hierarchy] ([HierarchyKey])
GO
ALTER TABLE [dbo].[Hierarchy] ADD CONSTRAINT [FK_Hierarchy_UniformTypeRef] FOREIGN KEY ([UniformType]) REFERENCES [dbo].[UniformTypeRef] ([UniformType])
GO